-- This file includes demos for the UNION [ALL] operator.
-- © Erland Sommarskog 2020.

USE SQLTitbits;
go
-- UNION [ALL] permits us to concatenate result sets from multiple queries
-- into a single result set.
-- This is an attempt to return all the configuration values in a single
-- column.
SELECT id, name, type, intval FROM dbo.ConfigValues WHERE type = 'int'
UNION
SELECT id, name, type, strval FROM dbo.ConfigValues WHERE type = 'str'
UNION
SELECT id, name, type, dateval FROM dbo.ConfigValues WHERE type = 'date'
UNION
SELECT id, name, type, datetimeval FROM dbo.ConfigValues WHERE type = 'dtm'
UNION
SELECT id, name, type, guidval FROM dbo.ConfigValues WHERE type = 'guid'; 
go

-- But the above failed, because all values in a column in a result set
-- must have the same data type. Therefore, convert all to something that
-- works for all types, for instance varchar. (sql_variant is better, but
-- (n)varchar is good enough.)
SELECT id, name, type, convert(varchar(10), intval) AS value
FROM   dbo.ConfigValues
WHERE  type = 'int'
UNION 
SELECT id, name, type, strval
FROM   dbo.ConfigValues
WHERE  type = 'str'
UNION 
SELECT id, name, type, convert(char(10), dateval)
FROM   dbo.ConfigValues
WHERE  type = 'date'
UNION 
SELECT id, name, type, convert(varchar(19), datetimeval, 121)
FROM   dbo.ConfigValues
WHERE  type = 'dtm'
UNION
SELECT id, name, type, convert(char(36), guidval)
FROM   dbo.ConfigValues
WHERE  type = 'guid';
go

-- Let's look closer how the UNION operator works. We create two tables.
-- Note that they have one value in common.
CREATE TABLE #alpha (n int NOT NULL);
INSERT #alpha (n) VALUES (1), (2), (3);
CREATE TABLE #beta (m int NOT NULL);
INSERT #beta (m) VALUES (3), (4), (5);
go
-- What happens with the value that is common to both tables? 
SELECT n 
FROM   #alpha
UNION
SELECT m 
FROM   #beta;
go

-- And some more values, that are duplicates.
INSERT #alpha(n) VALUES (12), (12), (12);
INSERT #beta(m) VALUES(15), (15);

-- Note what happens to the multiple 12 and 15.
SELECT n 
FROM   #alpha
UNION 
SELECT m 
FROM   #beta;

-- If you use UNION ALL, the duplicates are retained. You can comment
-- out the first ORDER BY to see what happens if you try that.
SELECT n 
FROM   #alpha
UNION ALL
SELECT m 
FROM   #beta
ORDER BY n;

-- UNION is a set operator, but few real-world data problems are actually
-- related to set theory. In practice we want UNION ALL 95% of the time.
-- In the few cases we actually want distinct values, express this explicitly.
SELECT DISTINCT n
FROM   (SELECT n 
        FROM   #alpha
        UNION  ALL
        SELECT m 
        FROM   #beta) AS u;


-- Here is the query on the ConfigValues. If you compare the query plans
-- for this one and the one above, you will find that they are different.
-- Generally, eliminating duplicates is costly, so help the optimizer
-- by saying ALL.
SELECT id, name, type, convert(varchar(10), intval) AS value
FROM   dbo.ConfigValues
WHERE  type = 'int'
UNION ALL
SELECT id, name, type, strval
FROM   dbo.ConfigValues
WHERE  type = 'str'
UNION ALL
SELECT id, name, type, convert(char(10), dateval)
FROM   dbo.ConfigValues
WHERE  type = 'date'
UNION ALL
SELECT id, name, type, convert(varchar(19), datetimeval, 121)
FROM   dbo.ConfigValues
WHERE  type = 'dtm'
UNION ALL
SELECT id, name, type, convert(char(36), guidval)
FROM   dbo.ConfigValues
WHERE  type = 'guid';
go

-- Two new tables. These are transactions from two different banks.
-- This, the transation IDs are completely unrelated.
SELECT * FROM dbo.EastTransactions;
SELECT * FROM dbo.WestTransactions;

-- Look at all transactions. Comment out the ALL. What happens? Could that
-- really be intentional? Recall that transaction IDs are unrelated.
SELECT TransactionID, Name, Qty, TransDate
FROM   dbo.EastTransactions
UNION --ALL
SELECT TransactionID, Name, Qty, TransDate
FROM   dbo.WestTransactions;

-- In most cases, you would something to identify the sources which makes the
-- two sets distinctive anyway.
SELECT 'East' AS Source, TransactionID, Name, Qty, TransDate
FROM   dbo.EastTransactions
UNION ALL
SELECT 'West' AS Source, TransactionID, Name, Qty, TransDate
FROM   dbo.WestTransactions;

-- Who did transactions on which date somewhere? Here UNION without ALL
-- makes sense.
SELECT Name, TransDate
FROM   dbo.EastTransactions
UNION  
SELECT Name, TransDate
FROM   dbo.WestTransactions;

-- Still it is better this way, since you make it clear that you want distinct
-- values. In the above, the reader may think you left out ALL by mistake.
SELECT DISTINCT Name, TransDate
FROM  (SELECT Name, TransDate
       FROM   dbo.EastTransactions
       UNION  ALL
       SELECT Name, TransDate
       FROM   dbo.WestTransactions) AS u;

-- Look at the transactions again, and then run the query below to compute
-- the sum of all transactions. Comment out the ALL, do you see the effect?
-- Would that result ever be desired?
SELECT * FROM dbo.EastTransactions;
SELECT * FROM dbo.WestTransactions;

SELECT Name, SUM(Qty) AS total
FROM   (SELECT Name, Qty
        FROM   dbo.EastTransactions
        UNION --ALL
        SELECT Name, Qty
        FROM   dbo.WestTransactions) AS u
GROUP BY Name;


